//--------------------------------------------------------------
//
// Qizhi Zhang 
// ezhung@gmail.com
// http://www.moorwind.com
//
//--------------------------------------------------------------
package reader.dao
{
	import __AS3__.vec.Vector;
	
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.utils.Dictionary;
	import flash.utils.setTimeout;
	
	import reader.model.vo.BlogItemVO;
	import reader.model.vo.BlogSourceVO;
	import reader.model.vo.UserVO;

	public class BlogDAO extends BaseDAO implements IBlogDAO
	{
		//////////////////////////////////////////////////////////////////////////////
		//SQLStatement queries
		
		private static const INSERT_BLOG:String = "INSERT INTO blog (blogSourceId, title, authors, link, pubDate, pubTime, guid, description, userId, creatDate) VALUES(:blogSourceId, :title, :authors, :link, :pubDate, :pubTime, :guid, :description, :userId, :creatDate)";
		private static const UPDATE_BLOG:String = "UPDATE blog SET isStar=:isStar, isRead=:isRead, isKeepUnread=:isKeepUnread WHERE blogId=:blogId";
		private static const UPDATE_BLOG_ISREAD:String = "UPDATE blog SET isRead=:isRead WHERE blogId=:blogId";
		private static const DELETE_BLOG:String = "DELETE FROM blog WHERE blogId=:blogId";
		private static const DELETE_BLOG_BY_BLOGSOURCE:String = "DELETE FROM blog WHERE blogSourceId=:blogSourceId";
		private static const DELETE_BLOG_BY_USER:String = "DELETE FROM blog WHERE userId=:userId";
		private static const SELETE_BLOG:String = "SELECT blogId, blogSourceId, title, authors, link, description, pubDate, isRead, isStar, isKeepUnread, creatDate FROM blog WHERE blogId=:blogId";
		private static const SELETE_BLOG_BY_BLOGSOURCE:String = "SELECT blogId, blogSourceId, title, authors, link, description, pubDate, isRead, isStar, isKeepUnread, creatDate FROM blog WHERE blogSourceId=:blogSourceId LIMIT :limit";
		private static const SELETE_BLOG_BY_BLOGSOURCE_UNREAD:String = "SELECT blogId, blogSourceId, title, authors, link, description, pubDate, isRead, isStar, isKeepUnread, creatDate FROM blog WHERE blogSourceId=:blogSourceId AND isRead=:isRead LIMIT :limit";
		private static const SELETE_BLOG_SUMMSRY:String = "SELECT blogSourceId, title, description FROM blog WHERE blogSourceId=:blogSourceId AND isRead=:isRead LIMIT :limit";
		private static const SELECT_BLOG_STAR:String = "SELECT blogId, blogSourceId, title, authors, link, description, pubDate, isRead, isStar, isKeepUnread, creatDate FROM blog WHERE userId=:userId AND isStar=:isStar LIMIT :limit";
		private static const SELECT_ALL_BLOG:String = "SELECT blogId, blogSourceId, title, authors, link, description, pubDate, isRead, isStar, isKeepUnread, creatDate FROM blog  WHERE userId=:userId LIMIT :limit";
		private static const SELECT_ALL_BLOG_UNREAD:String = "SELECT blogId, blogSourceId, title, authors, link, description, pubDate, isRead, isStar, isKeepUnread, creatDate FROM blog WHERE userId=:userId AND isRead=:isRead LIMIT :limit";
		private static const SELECT_COUNT:String = "SELECT COUNT(blogId) FROM blog WHERE userId=:userId";
		private static const SELECT_COUNT_UNREAD:String = "SELECT COUNT(blogId) FROM blog WHERE userId=:userId AND isRead=false";
		
		//////////////////////////////////////////////////////////////////////////////
		//constructor
		public function BlogDAO(connection:SQLConnection)
		{
			super(connection);
		}
		
		//////////////////////////////////////////////////////////////////////////////
		//methods
		
		//----------------------------------------------------------------------------
		//
		// INSERT
		//
		//----------------------------------------------------------------------------
		
		public function insertBlog(blog:BlogItemVO, user:UserVO):int
		{
			var obj:Object = {};
			obj.blogSourceId = blog.blogSourceId;
			obj.title = blog.title;
			obj.authors = blog.authors;
			obj.link = blog.link;
			obj.pubDate = blog.pubDate;
			obj.pubTime = blog.pubDate.getTime();
			obj.guid = blog.guid;
			obj.description = blog.description;
			obj.userId = user.userId;
			obj.creatDate = new Date();
			var sqlResult:SQLResult = execute(INSERT_BLOG, obj);
            return int(sqlResult.lastInsertRowID);
		}
		
		public function insertBlogs(blogs:Vector.<BlogItemVO>, user:UserVO):void
		{
			this.connection.begin();
			for each(var blog:BlogItemVO in blogs)
			{
				insertBlog(blog, user);
			}
			try
			{
				if(!this.connection.inTransaction)
				{
					this.connection.commit();
				}
				else
				{
					setTimeout(callLaterCommit, delayCommitInterval);
				}	
			}
			catch(e:Error)
			{
				trace(e);
			}
		}
		
		//----------------------------------------------------------------------------
		//
		// UPDATE
		//
		//----------------------------------------------------------------------------
		
		public function updateBlog(blog:BlogItemVO):Boolean
		{
			return update(UPDATE_BLOG, {isStar: blog.isStar, isRead: blog.isRead, isKeepUnread: blog.isKeepUnread, blogId: blog.blogId});
		}
		
		public function updateBlogIsRead(blog:BlogItemVO):Boolean
		{
			return update(UPDATE_BLOG_ISREAD, {isRead: blog.isRead, blogId: blog.blogId});
		}
		
		public function updateBlogs(blogs:Vector.<BlogItemVO>, user:UserVO):void
		{
			this.connection.begin();
			for each(var blog:BlogItemVO in blogs)
			{
				updateBlogIsRead(blog);
			}
			try
			{
				this.connection.commit();
			}
			catch(e:Error)
			{
				trace(e);
			}
		}
		
		//----------------------------------------------------------------------------
		//
		// DELETE
		//
		//----------------------------------------------------------------------------
		
		public function deleteBlog(blog:BlogItemVO):Boolean
		{
			return drop(DELETE_BLOG, {blogId: blog.blogId});
		}
		
		public function deleteBlogByBlogSource(blogsourceId:int):Boolean
		{
			return drop(DELETE_BLOG_BY_BLOGSOURCE, {blogSourceId: blogsourceId});
		}
		
		public function deleteBlogByUser(user:UserVO):Boolean
		{
			return drop(DELETE_BLOG_BY_USER, {userId: user.userId});
		}
		
		public function deleteBlogByBlogSources(blogsourceId:Vector.<BlogSourceVO>):void
		{
			this.connection.begin();
            for each(var bs:BlogSourceVO in blogsourceId)
            {
            	executeTransaction(DELETE_BLOG_BY_BLOGSOURCE, {blogSourceId: bs.blogSourceId});
            }
            
            try
            {
            	this.connection.commit();
            }
            catch(e:Error)
            {
            	trace(e);
            }
		}
		
		//----------------------------------------------------------------------------
		//
		// SELETE
		//
		//----------------------------------------------------------------------------
		
		//fetch
		public function selectBlog(blogid:int):BlogItemVO
		{
			return select(SELETE_BLOG, {blogId: blogid})[0];
		}
		
		public function selectBlogsByBlogSource(blogsourceId:int):Vector.<BlogItemVO>
		{
			return select(SELETE_BLOG_BY_BLOGSOURCE, {blogSourceId: blogsourceId, limit:MAX_DATA}, MAX_DATA);
		}
		
		public function selectBlogsByBlogSourceUnread(blogsourceId:int):Vector.<BlogItemVO>
		{
			return select(SELETE_BLOG_BY_BLOGSOURCE_UNREAD, {blogSourceId: blogsourceId, isRead: false, limit:MAX_DATA}, MAX_DATA);
		}
		
		public function selectBlogsStar(user:UserVO):Vector.<BlogItemVO>
		{
			return select(SELECT_BLOG_STAR, {isStar: true, userId: user.userId, limit:MAX_DATA}, MAX_DATA);
		}
		
		public function selectAllBlogs(user:UserVO):Vector.<BlogItemVO>
		{
			return select(SELECT_ALL_BLOG, {userId: user.userId, limit:MAX_DATA}, MAX_DATA);
		}
		
		public function selectAllBlogsUnread(user:UserVO):Vector.<BlogItemVO>
		{
			return select(SELECT_ALL_BLOG_UNREAD, {userId: user.userId, isRead: false, limit:MAX_DATA}, MAX_DATA);
		}
		
		public function selectByBlogSourceLabel(blogSourceIdArray:Vector.<int>):Vector.<BlogItemVO>
		{
			return select(createLabeldBlogStatement(blogSourceIdArray), {limit:MAX_DATA}, MAX_DATA);
		}
		
		public function selectByBlogSourceLabelUnread(blogSourceIdArray:Vector.<int>):Vector.<BlogItemVO>
		{
			return select(createLabeldBlogStatementUnread(blogSourceIdArray), {isRead:false, limit:MAX_DATA}, MAX_DATA);
		}
		
		//search
		public function searchBlog(keywords:String, user:UserVO, blogSourceIdArray:Vector.<int>):Vector.<BlogItemVO>
		{
			var state:String = user.isReadNew ? createSearchReadBlogStatement(keywords, blogSourceIdArray) : createSearchUnreadBlogStatement(keywords, blogSourceIdArray);
			return select(state, {userId: user.userId, limit:MAX_DATA}, MAX_DATA);
		}
		
		public function selectSummary(blogsource:Vector.<BlogSourceVO>, maxItems:int):Dictionary
		{
			if(!blogsource || blogsource.length == 0)
				return null;
			var result:Dictionary = new Dictionary();
			
			var i:int;
			var n:int = Math.min(maxItems, blogsource.length);
			for(i = 0; i < n; i++)
			{
				var bi:Vector.<BlogItemVO> = selectSummaryItem(blogsource[i].blogSourceId);
				if(bi && bi.length > 0) result[blogsource[i].blogSourceId] = bi;
			}
			return result;
		}
		
		private function selectSummaryItem(blogsourceId:int):Vector.<BlogItemVO>
		{
			var result:SQLResult = execute(SELETE_BLOG_SUMMSRY, {blogSourceId: blogsourceId, isRead:false, limit:3});
			if(!result || !result.data)
			{
				return null;
			}
			
			var dataArray:Vector.<BlogItemVO> = new Vector.<BlogItemVO>;
						
			for each(var obj:Object in result.data)
			{
				var BlogItem:BlogItemVO = new BlogItemVO();
				BlogItem.blogSourceId = obj.blogSourceId;
				BlogItem.title = obj.title;
				BlogItem.description = obj.description;
				dataArray.push(BlogItem);
			}
            return dataArray;
		}
		
		private function select(query:String, data:Object, prefetch:int = -1):Vector.<BlogItemVO>
		{
			var result:SQLResult = execute(query, data, prefetch);

			if(!result || !result.data)
			{
				return null;
			}
			
			var dataArray:Vector.<BlogItemVO> = new Vector.<BlogItemVO>();
			if(result.data.length > MAX_PREFETCH)
			{
				for each(var obj:Object in result.data)
				{
					var blog:BlogItemVO = new BlogItemVO();
					blog.sourceObject = obj;
					dataArray.push(blog);
				}
				return dataArray;
			}
			else
			{
				for each(var obj:Object in result.data)
				{
					dataArray.push(BlogItemVO.sourceObject2BlogItem(obj));
				}
	            return dataArray;
			}
		}
		
		private function createLabeldBlogStatementUnread(blogSourceIdArray:Vector.<int>):String
		{
			var s:String = "(";
			var a:Vector.<String> = new Vector.<String>;
			for each(var i:int in blogSourceIdArray)
			{
				a.push(" blogSourceId=" + i);
			}
			
			s += a.join(" OR ");
			s += " ) "
			
			return "SELECT blogId, blogSourceId, title, authors, link, description, pubDate, isRead, isStar, isKeepUnread, creatDate FROM blog WHERE isRead=:isRead AND " + s + " LIMIT :limit";
		}
		
		private function createLabeldBlogStatement(blogSourceIdArray:Vector.<int>):String
		{
			var s:String = "(";
			var a:Vector.<String> = new Vector.<String>;
			for each(var i:int in blogSourceIdArray)
			{
				a.push(" blogSourceId=" + i);
			}
			
			s += a.join(" OR ");
			s += " ) "
			
			return "SELECT blogId, blogSourceId, title, authors, link, description, pubDate, isRead, isStar, isKeepUnread, creatDate FROM blog WHERE " + s + " LIMIT :limit";
		}
		
		private function createSearchUnreadBlogStatement(keywords:String, blogSourceIdArray:Vector.<int>):String
		{
			var like:String = "\"%" + keywords + "%\"";
			var s:String = "(";
			var a:Vector.<String> = new Vector.<String>;
			for each(var i:int in blogSourceIdArray)
			{
				a.push(" blogSourceId=" + i);
			}
			
			s += a.join(" OR ");
			s += " ) "
			return "SELECT blogId, blogSourceId, title, authors, link, description, pubDate, isRead, isStar, isKeepUnread, creatDate FROM blog WHERE userId=:userId AND isRead=false AND " + s + " AND (title LIKE " + like + " OR authors LIKE " + like + ") LIMIT :limit"
		}
		
		private function createSearchReadBlogStatement(keywords:String, blogSourceIdArray:Vector.<int>):String
		{
			var like:String = "\"%" + keywords + "%\"";
			var s:String = "(";
			var a:Vector.<String> = new Vector.<String>;
			for each(var i:int in blogSourceIdArray)
			{
				a.push(" blogSourceId=" + i);
			}
			
			s += a.join(" OR ");
			s += " ) "
			return "SELECT blogId, blogSourceId, title, authors, link, description, pubDate, isRead, isStar, isKeepUnread, creatDate FROM blog WHERE userId=:userId AND" + s + " AND (title LIKE " + like + " OR authors LIKE " + like + ") LIMIT :limit"
		}
		
		//----------------------------------------------------------------------------
		//
		// COUNT
		//
		//----------------------------------------------------------------------------
		
		public function getCount(user:UserVO):uint
		{
			return count(SELECT_COUNT, {userId: user.userId});
		}
		
		public function getUnreadCount(user:UserVO):uint
		{
			return count(SELECT_COUNT_UNREAD, {userId: user.userId});
		}
	}
}